package org.cwnuit.train.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.cwnuit.train.domain.Trains;
import org.cwnuit.train.domain.Users;
import org.cwnuit.train.utils.ConnDB;
import org.cwnuit.train.utils.EntityUtil;
import org.cwnuit.train.utils.PageModel;

@SuppressWarnings("all")
public class UsersDAO {
	
	private static Logger logger = Logger.getLogger(UsersDAO.class.getName());   
	//添加用户信息
	public boolean addUser(String SQL) {
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		boolean flag = false;
		try {
			stmt.executeUpdate(SQL);
			flag = true;
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.closeStatement(stmt);
			ConnDB.closeConnection(conn);
		}
		return flag;
	}
	
	//删除用户信息
	public boolean deleteUser(String SQL) {
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		boolean flag = false;
		try {
			stmt.execute(SQL);
			flag = true;
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			ConnDB.closeStatement(stmt);
			ConnDB.closeConnection(conn);
		}
		return flag;
	}
	
	//修改用户信息
	public boolean updateUser(String SQL) {
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		boolean flag = false;
		try {
			stmt.executeUpdate(SQL);
			flag = true;
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			ConnDB.closeStatement(stmt);
			ConnDB.closeConnection(conn);
		}
		return flag;
	}
	
	// 查找User的方法
	public Users findUser(String SQL) {

		Users user = null;
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		ResultSet rs = ConnDB.getResultSet(stmt, SQL);

		try {
			while (rs.next()) {
				user = EntityUtil.RsToUser(rs);
			}
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.closeStatement(stmt);
			ConnDB.closeResultSet(rs);
			ConnDB.closeConnection(conn);
		}
		return user;
	}
	
	 //查找User的方法
	public List<Users> findUsers(String SQL) {

		List<Users> userslist = new ArrayList();
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		ResultSet rs = ConnDB.getResultSet(stmt, SQL);
		
		try {
			while(rs.next())
			{
				userslist.add(EntityUtil.RsToUser(rs));
			}
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			ConnDB.closeStatement(stmt);
			ConnDB.closeResultSet(rs);
			ConnDB.closeConnection(conn);
		}
		return userslist;
	}
	
	//分页查找用户信息
	public PageModel findByPage(int offset, int pagesize,String SQL) {
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		String tempSQL = SQL + " LIMIT " + offset + " , " + pagesize;
		ResultSet rs = ConnDB.getResultSet(stmt, tempSQL);
		
		PageModel pm = new PageModel();
		List<Users> userslist = new  ArrayList();
		try {
			while(rs.next())
			{
				userslist.add(EntityUtil.RsToUser(rs));	
			}
			pm.setTotal(countNum(SQL));
			pm.setDatas(userslist);
			logger.debug(tempSQL);
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally
		{
			ConnDB.closeStatement(stmt);
			ConnDB.closeResultSet(rs);
			ConnDB.closeConnection(conn);
		}
		return pm;
	}
	
	// 查找数据库中的记录数
	public int countNum(String SQL) {
		Connection conn = ConnDB.getConnection();
		Statement stmt = ConnDB.getStatement(conn);
		ResultSet rs = ConnDB.getResultSet(stmt, SQL);
		int counter = 0;
		try {
			while (rs.next()) {
				counter += 1;
			}
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.closeStatement(stmt);
			ConnDB.closeResultSet(rs);
			ConnDB.closeConnection(conn);
		}
		return counter;
	}
	
	// 用户登录
	public Users userLogin(String uname,String upass) {

		Users user = null;
		String SQL = "SELECT * FROM Users WHERE uname= ? AND upass = ?";
		Connection conn = ConnDB.getConnection();
		PreparedStatement pstmt = ConnDB.getPreparedStatement(conn, SQL);
		ResultSet rs = null;
		try {
			pstmt.setString(1, uname);
			pstmt.setString(2, upass);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				user = EntityUtil.RsToUser(rs);
			}
			logger.debug(SQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.closeResultSet(rs);
			ConnDB.closeStatement(pstmt);
			ConnDB.closeConnection(conn);
		}
		return user;
	}

}
